package ORM;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

public class Hero {
	private int id;
	private String name;
	private double hp;      //最大值为99999,小数点右侧最大位数为2
	private double damage;  //最大值为99999，小数点右侧最大位数为2
	public static String SQLLogin="jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8";
	public static String SQLUser = "root";
	public static String SQLPassword = "admin";
	public Hero(int id,String name,double hp,double damage) {
		this.id=id;
		this.name=name;
		this.hp = hp;
		this.damage = damage;
	}
	
	public void setId(int id ) {
		this.id = id;
	}
	
	public void setName(String name) {
		this.name= name;
	}
	
	public void setHp(double hp) {
		this.hp = hp;
	}
		
	public void setDamage(double damage) {
		this.damage = damage;
	}	
	
	//返回ID值
	public int getId() {
		return this.id;
	}
	
	public String getName() {
		return this.name;
	}

	public double getHp() {
		return this.hp;
	}
	
	public double getDamage() {
		return this.damage;
	}
	
	//从数据库java2sql.hero_orm中得到一个Hero类
	public static Hero getSQLHero(int id) {
		Hero hero = null;
		LoadDriver();
		
		//连接到本地SQL
		try(Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
			Statement s = c.createStatement();)
		{
			String sql = "select * from hero_orm where id ="+id;
			ResultSet rs = s.executeQuery(sql);
			if(rs.next()) {
				hero = new Hero(rs.getInt(1),rs.getString(2),rs.getFloat(3),rs.getFloat(4));
				//第一列为id，第二列为name，第三列为hp，第四列为damage
				System.out.println("已从数据库中加载id="+hero.getId()+"的数据到java内存中");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return hero;
	}
	
	//把JAVA中的Hero对象添加到数据库中
	public static void add2SQL(Hero hero) {		
		String sql = "insert into hero_orm values (?,?,?,?)";
		String sql1 = "select * from hero_orm where id ="+hero.getId();
		LoadDriver();
		
		try(Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
				PreparedStatement ps = c.prepareStatement(sql);
				Statement s = c.createStatement();)
			{
				ResultSet rs = s.executeQuery(sql1);
				if(!rs.next())    //判断数据库中是否存在相同ID的数据，若无则添加数据
				{
					ps.setInt(1, hero.getId());
					ps.setString(2, hero.getName());;
					ps.setDouble(3, hero.getHp());
					ps.setDouble(4, hero.getDamage());
					ps.execute();
					System.out.println("已从java中加载id="+hero.getId()+"的数据到数据库中");
				}
				else {
					System.out.println("数据库中已存在id="+hero.getId()+"的数据");
					return ;
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
		Hero.SQLSize();
	}
	
	//把这个Hero对象对应的数据删除掉
	public static void delete(Hero hero) {

		LoadDriver();
		
		try(Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
				Statement s = c.createStatement();)
			{
				String sql = "delete from hero_orm where id ="+hero.getId();
				s.execute(sql);
				System.out.println("已从数据库中删除id="+hero.getId()+"的数据");
			}catch(SQLException e) {
				e.printStackTrace();
			}
		Hero.SQLSize();
	}
	
	//更新这个Hero对象对应的数据
	public static void update2SQL(Hero hero) {
		LoadDriver();
		
		String sql = "update hero_orm set name="+"'"+hero.name+"' "+",hp="+hero.getHp()+" ,damage="+hero.getDamage()+" where id="+hero.getId();
		String sql1 = "select * from hero_orm where id ="+hero.getId();
		//System.out.println(sql);
		
		try(Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
				Statement s = c.createStatement();
				Statement s1 = c.createStatement();)
			{
				ResultSet rs = s.executeQuery(sql1);
				if(rs.next())   //判断数据库中是否存在该ID的数据，若有则进行update 
				{
					s1.execute(sql);
					System.out.println("已从数据库中更新id="+hero.getId()+"的数据");
				}
				
			}catch(SQLException e) {
				e.printStackTrace();
			}
	}
	
	//把数据库中所有的数据转换为Hero对象后，存储到list中
	public static List<Hero> list(){
		List<Hero> list = new ArrayList<Hero>();
		String sql = "select * from hero_orm";
		LoadDriver();
		
		try(Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
				Statement s = c.createStatement();)
			{
				ResultSet rs = s.executeQuery(sql);
				while(rs.next()) {
					Hero hero = new Hero(rs.getInt(1),rs.getString(2),rs.getFloat(3),rs.getFloat(4));
					list.add(hero);
				}
				
			}catch(SQLException e) {
				e.printStackTrace();
			}	
		return list;
	}

	//检查表Hero_orm的列数
	public static int SQLSize() {
		int size = 0;
		LoadDriver();
		String sql = "select count(*) from hero_orm";
		try (Connection c = DriverManager.getConnection(SQLLogin,SQLUser,SQLPassword);
			Statement s = c.createStatement();){
			ResultSet rs=s.executeQuery(sql);
			if(rs.next()) {
				size = rs.getInt(1);
			}
			System.out.println("数据库中总共有"+size+"条数据");
		}catch(SQLException e){
			e.printStackTrace();
		}
		return size;
	}
	
	public static void LoadDriver() {
		//加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

